﻿CREATE PROCEDURE sh_Build_v_Tracking AS
DECLARE @Pos int SET @Pos = 0
DECLARE @ID sysname,@Lable sysname
DECLARE @SQL nvarchar(max) SET @SQL = ''

--WHILE @@ROWCOUNT > 0 BEGIN
--	SELECT TOP 1 @Pos=Pos,@SQL=@SQL+'['+CONVERT(varchar,ID)+'] ['+Lable+'],'+CONVERT(varchar,Pos) FROM t_Verification WHERE ParentID = 63 AND Pos > @Pos ORDER BY Pos
--END

EXEC sp__Join 'SELECT ''[''+CONVERT(varchar,ID)+''] [''+Lable+'']'' FROM t_Verification WHERE ParentID = 63 ORDER BY Pos',',
',@SQL OUTPUT

DECLARE @Fields nvarchar(max)
EXEC sp__Join 'SELECT ''[''+CONVERT(varchar,ID)+'']'' FROM t_Verification WHERE ParentID = 63 ORDER BY Pos',',
',@Fields OUTPUT

EXEC(
'
ALTER VIEW v_Tracking AS
SELECT
ApplicantID,
'+@SQL+
'FROM
(	SELECT LoanID, VerificationID, CASE Checked WHEN 1 THEN dbo.Date_Format(DateStamp,5) ELSE NULL END DateStamp
  FROM   t_Loan_Verification
)AV
PIVOT(MAX(DateStamp)FOR VerificationID
IN(
'+@Fields+
')
) AS pvt
'
)

